package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.ICountGrantryService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;

@Service("countGrantryService")
public class CountGrantryService implements ICountGrantryService {
    Connection connPre = null;

    @Override
    public void transfer() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作===");
        final String recordName = "tb_sync_gbupload_etctu";
        final String recordNameGrantryHour = "tb_sync_gbupload_etcsu";
        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            String recordStr = DBUtils.getCurSynRecordNum(connPre, recordName);
            XxlJobLogger.log("[门架完整性]本次门架流水最大记录RECORDID为:" + recordStr);

            String recordStrGrantryHour = DBUtils.getCurSynRecordNum(connPre, recordNameGrantryHour);
            XxlJobLogger.log("[门架完整性]本次门架站级统计最大记录RECORDID为:" + recordStrGrantryHour);

            String dbCurTime = DBUtils.getDBCurTime(connPre);
            XxlJobLogger.log("[门架完整性]本次同步截止时间为:" + dbCurTime);

            StringBuffer sql = new StringBuffer();
            sql.append("insert into TB_SYNC_GBUPLOAD_ETCTU_TMP(id,mediatype,mediano,obusign,cardid,etccardtype,vehicleid,vehicletype,vehicleclass," +
                    "entolllaneid,entolllanehex,entime,passid,vehiclesign,stationno,tollgantryid,gantrytype,gantryhex,payfee,fee,discountfee,transtime," +
                    "transfee,hourbatchno,transferstatus,receivetime,recordid,inserttime,batchno,domain1,domain2,domain3,domain4) " +
                    "select id,mediatype,mediano,obusign,cardid,etccardtype,vehicleid,vehicletype,vehicleclass," +
                    "entolllaneid,entolllanehex,entime,passid,vehiclesign,stationno,tollgantryid,gantrytype,gantryhex,payfee,fee,discountfee,transtime," +
                    "transfee,hourbatchno,transferstatus,receivetime,recordid,inserttime,batchno,domain1,domain2,domain3,domain4 " +
                    "from TB_SYNC_GBUPLOAD_ETCTU " +
                    "where hourbatchno is not null and inserttime>=to_date('").append(recordStr).append("','yyyy-mm-dd hh24:mi:ss') and inserttime<to_date('").append(dbCurTime).append("','yyyy-mm-dd hh24:mi:ss') ");
            Integer count = DBUtils.execute(connPre, sql.toString());
            XxlJobLogger.log("[门架完整性]本次门架流水同步到临时表(条):" + count);

            count = DBUtils.modifySynRecordNum(connPre, recordName, recordStr, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[门架完整性异常]更新记录时间异常");
                throw new Exception("[门架完整性异常]更新记录时间异常");
            }
            XxlJobLogger.log("[门架完整性]更新记录时间为:" + dbCurTime);

            String sqlPro = "insert into TB_STATIS_GRANTRY_BATCH_TMP(trade_day,road_id,station_id,road_name,station_name,grantry_no,hour_batch_no," +
                    "totalcnt,etc_cnt,etc_money,cpc_cnt,cpc_money,receive_time,update_time) " +
                    "select TMP.tjdate,d.road_id,c.station_id,d.road_name,c.station_name," +
                    "TMP.tollgantryid,TMP.hourBatchNo,TMP.TOTALCNT,TMP.ETC_CNT,TMP.ETC_MONEY,TMP.CPC_CNT,TMP.CPC_MONEY,sysdate,sysdate " +
                    "from (" +
                    "select substr(hourBatchNo,1,4)||'-'||substr(hourBatchNo,5,2)||'-'||substr(hourBatchNo,7,2) as tjdate," +
                    "a.tollgantryid,a.hourBatchNo," +
                    "sum(1) as TOTALCNT," +
                    "sum(case when mediaType=1 then 1 else 0 end) as ETC_CNT," +
                    "sum(case when mediaType=1 then fee else 0 end) as ETC_MONEY," +
                    "sum(case when mediaType=2 then 1 else 0 end) as CPC_CNT," +
                    "sum(case when mediaType=2 then fee else 0 end) as CPC_MONEY " +
                    "from tb_sync_gbupload_etctu_tmp a " +
                    "group by a.tollgantryid,a.hourBatchNo ) TMP " +
                    "left join tb_charge_grantry_station b on TMP.tollgantryid=b.grantry_id " +
                    "left join tb_charge_station c on c.station_id=b.station_id " +
                    "left join tb_charge_road d on d.road_id=c.road_id ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]本次共生成省接收门架统计数据(条):" + count);

            sqlPro = "insert into TB_SYNC_GBUPLOAD_ETCSU_TMP1(recordid,collectid,gantryid,computerorder,collectdate,collecthourbatch," +
                    "etctypecount,etcclasscount,etcsuccesscount,etcsuccessfee,etcfailcount,etcfailfee,cpctypecount,cpcclasscount,cpcsuccesscount," +
                    "cpcsuccessfee,cpcfailcount,cpcfailfee,detectioncount,vehicledatacount,vehiclepiccount,transfertag,receivetime,packname," +
                    "batchcount,inserttime,updatetime,domain1,domain2,domain3,domain4) " +
                    "select recordid,collectid,gantryid,computerorder,collectdate,collecthourbatch," +
                    "etctypecount,etcclasscount,etcsuccesscount,etcsuccessfee,etcfailcount,etcfailfee,cpctypecount,cpcclasscount,cpcsuccesscount," +
                    "cpcsuccessfee,cpcfailcount,cpcfailfee,detectioncount,vehicledatacount,vehiclepiccount,transfertag,receivetime,packname," +
                    "batchcount,inserttime,updatetime,domain1,domain2,domain3,domain4 " +
                    "from TB_SYNC_GBUPLOAD_ETCSU a " +
                    "where a.INSERTTIME>=to_date('" + recordStrGrantryHour + "','yyyy-mm-dd hh24:mi:ss') and a.INSERTTIME<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]门架小时批次临时表1共同步(条):" + count);

            sqlPro = "insert into TB_SYNC_GBUPLOAD_ETCSU_TMP2(recordid,collectid,gantryid,computerorder,collectdate,collecthourbatch," +
                    "etctypecount,etcclasscount,etcsuccesscount,etcsuccessfee,etcfailcount,etcfailfee,cpctypecount,cpcclasscount,cpcsuccesscount," +
                    "cpcsuccessfee,cpcfailcount,cpcfailfee,detectioncount,vehicledatacount,vehiclepiccount,transfertag,receivetime,packname," +
                    "batchcount,inserttime,updatetime,domain1,domain2,domain3,domain4) " +
                    "select recordid,collectid,gantryid,computerorder,collectdate,collecthourbatch," +
                    "etctypecount,etcclasscount,etcsuccesscount,etcsuccessfee,etcfailcount,etcfailfee,cpctypecount,cpcclasscount,cpcsuccesscount," +
                    "cpcsuccessfee,cpcfailcount,cpcfailfee,detectioncount,vehicledatacount,vehiclepiccount,transfertag,receivetime,packname," +
                    "batchcount,inserttime,updatetime,domain1,domain2,domain3,domain4 " +
                    "from TB_SYNC_GBUPLOAD_ETCSU_TMP1 a where exists (select 1 from (" +
                    "select max(RECORDID) RECORDID from TB_SYNC_GBUPLOAD_ETCSU_TMP1 group by GANTRYID,COLLECTHOURBATCH ) tmp " +
                    "where a.RECORDID=tmp.RECORDID) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]门架小时批次临时表2共同步(条):" + count);

            sqlPro = "MERGE INTO TB_STATIS_GRANTRY_BATCH_TMP A USING (" +
                    "  select substr(a.collectDate,1,4)||'-'||substr(a.collectDate,5,2)||'-'||substr(a.collectDate,7,2) as collectDate," +
                    "  d.road_id,c.station_id,d.road_name,c.station_name,a.gantryid,a.collectHourBatch," +
                    "  batchCount as TOTALCNT," +
                    "  nvl(etcSuccessCount,0) + nvl(etcFailCount,0) as ETC_CNT," +
                    "  nvl(etcsuccessfee,0) + nvl(etcfailfee,0) as ETC_MONEY," +
                    "  nvl(cpcsuccesscount,0) + nvl(cpcfailcount,0) as CPC_CNT," +
                    "  nvl(cpcsuccessfee,0) + nvl(cpcfailfee,0) as CPC_MONEY " +
                    "  from TB_SYNC_GBUPLOAD_ETCSU_TMP2 a " +
                    "  left join tb_charge_grantry_station b on a.gantryid=b.grantry_id " +
                    "  left join tb_charge_station c on c.station_id=b.station_id " +
                    "  left join tb_charge_road d on d.road_id=c.road_id " +
                    ") TMP " +
                    "ON (A.grantry_no=TMP.gantryid and A.hour_batch_no=TMP.collectHourBatch) " +
                    "WHEN MATCHED THEN " +
                    "UPDATE SET A.TRADE_DAY=TMP.COLLECTDATE,A.ROAD_ID=TMP.ROAD_ID,A.STATION_ID=TMP.STATION_ID,A.ROAD_NAME=TMP.ROAD_NAME," +
                    "  A.STATION_NAME=TMP.STATION_NAME,A.GRANTRY_TOTALCNT=TMP.TOTALCNT," +
                    "  A.GRANTRY_ETC_CNT=TMP.ETC_CNT,A.GRANTRY_ETC_MONEY=TMP.ETC_MONEY," +
                    "  A.GRANTRY_CPC_CNT=TMP.CPC_CNT,A.GRANTRY_CPC_MONEY=TMP.CPC_MONEY " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(A.TRADE_DAY,A.ROAD_ID,A.STATION_ID,A.ROAD_NAME,A.STATION_NAME,A.GRANTRY_NO,A.HOUR_BATCH_NO,A.GRANTRY_TOTALCNT," +
                    "  A.GRANTRY_ETC_CNT,A.GRANTRY_ETC_MONEY,A.GRANTRY_CPC_CNT,A.GRANTRY_CPC_MONEY) " +
                    "  VALUES(TMP.COLLECTDATE,TMP.ROAD_ID,TMP.STATION_ID,TMP.ROAD_NAME,TMP.STATION_NAME,TMP.GANTRYID,TMP.COLLECTHOURBATCH,TMP.TOTALCNT," +
                    "  TMP.ETC_CNT,TMP.ETC_MONEY,TMP.CPC_CNT,TMP.CPC_MONEY) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]本次共混合站级临时统计数据(条):" + count);

            count = DBUtils.modifySynRecordNum(connPre, recordNameGrantryHour, recordStrGrantryHour, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[门架完整性异常]更新记录时间异常");
                throw new Exception("[门架完整性异常]更新记录时间异常");
            }
            XxlJobLogger.log("[门架完整性]更新记录时间为:" + dbCurTime);

            //合并临时完整性表到正式表
            sqlPro = "MERGE INTO TB_STATIS_GRANTRY_BATCH A USING TB_STATIS_GRANTRY_BATCH_TMP B " +
                    "ON (A.grantry_no=B.grantry_no and A.hour_batch_no=B.hour_batch_no) " +
                    "WHEN MATCHED THEN " +
                    "UPDATE SET A.ROAD_ID=B.ROAD_ID,A.STATION_ID=B.STATION_ID,A.ROAD_NAME=B.ROAD_NAME," +
                    "  A.STATION_NAME=B.STATION_NAME," +
                    "  A.GRANTRY_TOTALCNT=nvl((case when B.GRANTRY_TOTALCNT is null then A.GRANTRY_TOTALCNT else null end),0)+nvl(B.GRANTRY_TOTALCNT,0)," +//如果小时汇总B表GRANTRY_TOTALCNT为null则A表不变，否则赋值为B表的GRANTRY_TOTALCNT
                    "  A.GRANTRY_ETC_CNT=nvl((case when B.GRANTRY_ETC_CNT is null then A.GRANTRY_ETC_CNT else null end),0)+nvl(B.GRANTRY_ETC_CNT,0)," +
                    "  A.GRANTRY_ETC_MONEY=nvl((case when B.GRANTRY_ETC_MONEY is null then A.GRANTRY_ETC_MONEY else null end),0)+nvl(B.GRANTRY_ETC_MONEY,0)," +
                    "  A.GRANTRY_CPC_CNT=nvl((case when B.GRANTRY_CPC_CNT is null then A.GRANTRY_CPC_CNT else null end),0)+nvl(B.GRANTRY_CPC_CNT,0)," +
                    "  A.GRANTRY_CPC_MONEY=nvl((case when B.GRANTRY_CPC_MONEY is null then A.GRANTRY_CPC_MONEY else null end),0)+nvl(B.GRANTRY_CPC_MONEY,0)," +
                    "  A.TOTALCNT=nvl(A.TOTALCNT,0)+nvl(B.TOTALCNT,0),A.ETC_CNT=nvl(A.ETC_CNT,0)+nvl(B.ETC_CNT,0),A.ETC_MONEY=nvl(A.ETC_MONEY,0)+nvl(B.ETC_MONEY,0)," +
                    "  A.CPC_CNT=nvl(A.CPC_CNT,0)+nvl(B.CPC_CNT,0),A.CPC_MONEY=nvl(A.CPC_MONEY,0)+nvl(B.CPC_MONEY,0),A.UPDATE_TIME=sysdate " +
                    "WHEN NOT MATCHED THEN " +
                    "INSERT (A.SEQ_ID,A.TRADE_DAY,A.ROAD_ID,A.STATION_ID,A.ROAD_NAME,A.STATION_NAME,A.GRANTRY_NO,A.HOUR_BATCH_NO," +
                    "  A.GRANTRY_TOTALCNT,A.GRANTRY_ETC_CNT,A.GRANTRY_ETC_MONEY,A.GRANTRY_CPC_CNT,A.GRANTRY_CPC_MONEY,A.TOTALCNT," +
                    "  A.ETC_CNT,A.ETC_MONEY,A.CPC_CNT,A.CPC_MONEY,A.RECEIVE_TIME,A.UPDATE_TIME) " +
                    "  VALUES(SEQ_TB_STATIS_GRANTRY_BATCH.NEXTVAL,B.TRADE_DAY,B.ROAD_ID,B.STATION_ID,B.ROAD_NAME,B.STATION_NAME,B.GRANTRY_NO,B.HOUR_BATCH_NO," +
                    "  B.GRANTRY_TOTALCNT,B.GRANTRY_ETC_CNT,B.GRANTRY_ETC_MONEY,B.GRANTRY_CPC_CNT,B.GRANTRY_CPC_MONEY,B.TOTALCNT," +
                    "  B.ETC_CNT,B.ETC_MONEY,B.CPC_CNT,B.CPC_MONEY,sysdate,sysdate)";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]本次共混合站级正式统计数据(条):" + count);

//            connPre.commit();

            sqlPro = "insert into TB_STATIS_GRANTRY_CARFLOW_TMP(gantryid,gantrytype,transdate,vehicleType,countNum,fee) " +
                    "select tollgantryid,gantrytype,substr(transTime,1,10) transdate,vehicleType,count(1) n,sum(fee) fee " +
                    "from TB_SYNC_GBUPLOAD_ETCTU_TMP a " +
                    "where gantryType in (2,3) " +
                    "group by tollgantryid,gantrytype,substr(transTime,1,10),vehicleType ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架统计分析]本次共生成省接收门架统计数据(条):" + count);

            sqlPro = "MERGE INTO TB_STATIS_GANTRY_ENCARFLOW A USING (" +
                    "select GANTRYID,TRANSDATE,station_id,road_id,GRANTRY_NAME," +
                    "\"1_FLOW\",\"2_FLOW\",\"3_FLOW\",\"4_FLOW\", " +
                    "\"11_FLOW\",\"12_FLOW\",\"13_FLOW\",\"14_FLOW\",\"15_FLOW\",\"16_FLOW\", " +
                    "\"21_FLOW\",\"22_FLOW\",\"23_FLOW\",\"24_FLOW\",\"25_FLOW\",\"26_FLOW\" " +
                    "from (" +
                    "   select GANTRYID,TRANSDATE,VEHICLETYPE,COUNTNUM,p.station_id,q.road_id,p.GRANTRY_NAME from TB_STATIS_GRANTRY_CARFLOW_TMP o " +
                    "   left join tb_charge_grantry_station p on o.gantryid=p.grantry_id " +
                    "   left join tb_charge_station q on p.station_id=q.station_id " +
                    "   where GANTRYTYPE=2 ) " +
                    "   pivot (sum(nvl(COUNTNUM,0)) FLOW " +
                    "   for VEHICLETYPE in (" +
                    "     1 as \"1\",2 as \"2\",3 as \"3\",4 as \"4\"," +
                    "     11 as \"11\",12 as \"12\",13 as \"13\",14 as \"14\",15 as \"15\",16 as \"16\"," +
                    "     21 as \"21\",22 as \"22\",23 as \"23\",24 as \"24\",25 as \"25\",26 as \"26\")) " +
                    ") B " +
                    "ON (A.GANTRY_ID=B.GANTRYID and A.TRADE_DATE=B.TRANSDATE) " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET A.UPDATE_TIME=SYSDATE,A.VEHICLE_FLOW_1=nvl(A.VEHICLE_FLOW_1,0)+B.\"1_FLOW\",A.VEHICLE_FLOW_2=nvl(A.VEHICLE_FLOW_2,0)+B.\"2_FLOW\",A.VEHICLE_FLOW_3=nvl(A.VEHICLE_FLOW_3,0)+B.\"3_FLOW\",A.VEHICLE_FLOW_4=nvl(A.VEHICLE_FLOW_4,0)+B.\"4_FLOW\"," +
                    "  A.VEHICLE_FLOW_11=nvl(A.VEHICLE_FLOW_11,0)+B.\"11_FLOW\",A.VEHICLE_FLOW_12=nvl(A.VEHICLE_FLOW_12,0)+B.\"12_FLOW\",A.VEHICLE_FLOW_13=nvl(A.VEHICLE_FLOW_13,0)+B.\"13_FLOW\",A.VEHICLE_FLOW_14=nvl(A.VEHICLE_FLOW_14,0)+B.\"14_FLOW\",A.VEHICLE_FLOW_15=nvl(A.VEHICLE_FLOW_15,0)+B.\"15_FLOW\",A.VEHICLE_FLOW_16=nvl(A.VEHICLE_FLOW_16,0)+B.\"16_FLOW\"," +
                    "  A.VEHICLE_FLOW_21=nvl(A.VEHICLE_FLOW_21,0)+B.\"21_FLOW\",A.VEHICLE_FLOW_22=nvl(A.VEHICLE_FLOW_22,0)+B.\"22_FLOW\",A.VEHICLE_FLOW_23=nvl(A.VEHICLE_FLOW_23,0)+B.\"23_FLOW\",A.VEHICLE_FLOW_24=nvl(A.VEHICLE_FLOW_24,0)+B.\"24_FLOW\",A.VEHICLE_FLOW_25=nvl(A.VEHICLE_FLOW_25,0)+B.\"25_FLOW\",A.VEHICLE_FLOW_26=nvl(A.VEHICLE_FLOW_26,0)+B.\"26_FLOW\" " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(A.GANTRY_ID,A.GANTRY_NAME,A.STATION_ID,A.ROAD_ID,A.TRADE_DATE,A.INSERT_TIME,A.UPDATE_TIME," +
                    "  A.VEHICLE_FLOW_1,A.VEHICLE_FLOW_2,A.VEHICLE_FLOW_3,A.VEHICLE_FLOW_4," +
                    "  A.VEHICLE_FLOW_11,A.VEHICLE_FLOW_12,A.VEHICLE_FLOW_13,A.VEHICLE_FLOW_14,A.VEHICLE_FLOW_15,A.VEHICLE_FLOW_16," +
                    "  A.VEHICLE_FLOW_21,A.VEHICLE_FLOW_22,A.VEHICLE_FLOW_23,A.VEHICLE_FLOW_24,A.VEHICLE_FLOW_25,A.VEHICLE_FLOW_26) " +
                    "  VALUES(B.GANTRYID,B.GRANTRY_NAME,B.station_id,B.road_id,b.TRANSDATE,SYSDATE,SYSDATE," +
                    "  nvl(B.\"1_FLOW\",0),nvl(B.\"2_FLOW\",0),nvl(B.\"3_FLOW\",0),nvl(B.\"4_FLOW\",0)," +
                    "  nvl(B.\"11_FLOW\",0),nvl(B.\"12_FLOW\",0),nvl(B.\"13_FLOW\",0),nvl(B.\"14_FLOW\",0),nvl(B.\"15_FLOW\",0),nvl(B.\"16_FLOW\",0)," +
                    "  nvl(B.\"21_FLOW\",0),nvl(B.\"22_FLOW\",0),nvl(B.\"23_FLOW\",0),nvl(B.\"24_FLOW\",0),nvl(B.\"25_FLOW\",0),nvl(B.\"26_FLOW\",0)) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架统计分析]合并更新入口门架车流量(条):" + count);

            sqlPro = "MERGE INTO TB_STATIS_GANTRY_EXTRADE A USING (" +
                    "select GANTRYID,TRANSDATE,station_id,road_id,GRANTRY_NAME," +
                    "\"1_FLOW\",\"2_FLOW\",\"3_FLOW\",\"4_FLOW\", " +
                    "\"11_FLOW\",\"12_FLOW\",\"13_FLOW\",\"14_FLOW\",\"15_FLOW\",\"16_FLOW\", " +
                    "\"21_FLOW\",\"22_FLOW\",\"23_FLOW\",\"24_FLOW\",\"25_FLOW\",\"26_FLOW\", " +
                    "\"1_FEE\",\"2_FEE\",\"3_FEE\",\"4_FEE\", " +
                    "\"11_FEE\",\"12_FEE\",\"13_FEE\",\"14_FEE\",\"15_FEE\",\"16_FEE\", " +
                    "\"21_FEE\",\"22_FEE\",\"23_FEE\",\"24_FEE\",\"25_FEE\",\"26_FEE\" " +
                    "from (" +
                    "   select GANTRYID,TRANSDATE,VEHICLETYPE,COUNTNUM,FEE,p.station_id,q.road_id,p.GRANTRY_NAME from TB_STATIS_GRANTRY_CARFLOW_TMP o " +
                    "   left join tb_charge_grantry_station p on o.gantryid=p.grantry_id " +
                    "   left join tb_charge_station q on p.station_id=q.station_id " +
                    "   where GANTRYTYPE=3 ) " +
                    "   pivot (sum(nvl(COUNTNUM,0)) FLOW ,sum(nvl(FEE,0)) FEE " +
                    "   for VEHICLETYPE in (" +
                    "     1 as \"1\",2 as \"2\",3 as \"3\",4 as \"4\"," +
                    "     11 as \"11\",12 as \"12\",13 as \"13\",14 as \"14\",15 as \"15\",16 as \"16\"," +
                    "     21 as \"21\",22 as \"22\",23 as \"23\",24 as \"24\",25 as \"25\",26 as \"26\")) " +
                    ") B " +
                    "ON (A.GANTRY_ID=B.GANTRYID and A.TRADE_DATE=B.TRANSDATE) " +
                    "WHEN MATCHED THEN " +
                    "  UPDATE SET A.UPDATE_TIME=SYSDATE,A.VEHICLE_FLOW_1=nvl(A.VEHICLE_FLOW_1,0)+B.\"1_FLOW\",A.VEHICLE_FLOW_2=nvl(A.VEHICLE_FLOW_2,0)+B.\"2_FLOW\",A.VEHICLE_FLOW_3=nvl(A.VEHICLE_FLOW_3,0)+B.\"3_FLOW\",A.VEHICLE_FLOW_4=nvl(A.VEHICLE_FLOW_4,0)+B.\"4_FLOW\"," +
                    "  A.VEHICLE_FLOW_11=nvl(A.VEHICLE_FLOW_11,0)+B.\"11_FLOW\",A.VEHICLE_FLOW_12=nvl(A.VEHICLE_FLOW_12,0)+B.\"12_FLOW\",A.VEHICLE_FLOW_13=nvl(A.VEHICLE_FLOW_13,0)+B.\"13_FLOW\",A.VEHICLE_FLOW_14=nvl(A.VEHICLE_FLOW_14,0)+B.\"14_FLOW\",A.VEHICLE_FLOW_15=nvl(A.VEHICLE_FLOW_15,0)+B.\"15_FLOW\",A.VEHICLE_FLOW_16=nvl(A.VEHICLE_FLOW_16,0)+B.\"16_FLOW\"," +
                    "  A.VEHICLE_FLOW_21=nvl(A.VEHICLE_FLOW_21,0)+B.\"21_FLOW\",A.VEHICLE_FLOW_22=nvl(A.VEHICLE_FLOW_22,0)+B.\"22_FLOW\",A.VEHICLE_FLOW_23=nvl(A.VEHICLE_FLOW_23,0)+B.\"23_FLOW\",A.VEHICLE_FLOW_24=nvl(A.VEHICLE_FLOW_24,0)+B.\"24_FLOW\",A.VEHICLE_FLOW_25=nvl(A.VEHICLE_FLOW_25,0)+B.\"25_FLOW\",A.VEHICLE_FLOW_26=nvl(A.VEHICLE_FLOW_26,0)+B.\"26_FLOW\"," +
                    "  A.VEHICLE_FEE_1=nvl(A.VEHICLE_FEE_1,0)+B.\"1_FEE\",A.VEHICLE_FEE_2=nvl(A.VEHICLE_FEE_2,0)+B.\"2_FEE\",A.VEHICLE_FEE_3=nvl(A.VEHICLE_FEE_3,0)+B.\"3_FEE\",A.VEHICLE_FEE_4=nvl(A.VEHICLE_FEE_4,0)+B.\"4_FEE\"," +
                    "  A.VEHICLE_FEE_11=nvl(A.VEHICLE_FEE_11,0)+B.\"11_FEE\",A.VEHICLE_FEE_12=nvl(A.VEHICLE_FEE_12,0)+B.\"12_FEE\",A.VEHICLE_FEE_13=nvl(A.VEHICLE_FEE_13,0)+B.\"13_FEE\",A.VEHICLE_FEE_14=nvl(A.VEHICLE_FEE_14,0)+B.\"14_FEE\",A.VEHICLE_FEE_15=nvl(A.VEHICLE_FEE_15,0)+B.\"15_FEE\",A.VEHICLE_FEE_16=nvl(A.VEHICLE_FEE_16,0)+B.\"16_FEE\"," +
                    "  A.VEHICLE_FEE_21=nvl(A.VEHICLE_FEE_21,0)+B.\"21_FEE\",A.VEHICLE_FEE_22=nvl(A.VEHICLE_FEE_22,0)+B.\"22_FEE\",A.VEHICLE_FEE_23=nvl(A.VEHICLE_FEE_23,0)+B.\"23_FEE\",A.VEHICLE_FEE_24=nvl(A.VEHICLE_FEE_24,0)+B.\"24_FEE\",A.VEHICLE_FEE_25=nvl(A.VEHICLE_FEE_25,0)+B.\"25_FEE\",A.VEHICLE_FEE_26=nvl(A.VEHICLE_FEE_26,0)+B.\"26_FEE\" " +
                    "WHEN NOT MATCHED THEN " +
                    "  INSERT(A.GANTRY_ID,A.GANTRY_NAME,A.STATION_ID,A.ROAD_ID,A.TRADE_DATE,A.INSERT_TIME,A.UPDATE_TIME," +
                    "  A.VEHICLE_FLOW_1,A.VEHICLE_FLOW_2,A.VEHICLE_FLOW_3,A.VEHICLE_FLOW_4," +
                    "  A.VEHICLE_FLOW_11,A.VEHICLE_FLOW_12,A.VEHICLE_FLOW_13,A.VEHICLE_FLOW_14,A.VEHICLE_FLOW_15,A.VEHICLE_FLOW_16," +
                    "  A.VEHICLE_FLOW_21,A.VEHICLE_FLOW_22,A.VEHICLE_FLOW_23,A.VEHICLE_FLOW_24,A.VEHICLE_FLOW_25,A.VEHICLE_FLOW_26," +
                    "  A.VEHICLE_FEE_1,A.VEHICLE_FEE_2,A.VEHICLE_FEE_3,A.VEHICLE_FEE_4," +
                    "  A.VEHICLE_FEE_11,A.VEHICLE_FEE_12,A.VEHICLE_FEE_13,A.VEHICLE_FEE_14,A.VEHICLE_FEE_15,A.VEHICLE_FEE_16," +
                    "  A.VEHICLE_FEE_21,A.VEHICLE_FEE_22,A.VEHICLE_FEE_23,A.VEHICLE_FEE_24,A.VEHICLE_FEE_25,A.VEHICLE_FEE_26) " +
                    "  VALUES(B.GANTRYID,B.GRANTRY_NAME,B.station_id,B.road_id,b.TRANSDATE,SYSDATE,SYSDATE," +
                    "  nvl(B.\"1_FLOW\",0),nvl(B.\"2_FLOW\",0),nvl(B.\"3_FLOW\",0),nvl(B.\"4_FLOW\",0)," +
                    "  nvl(B.\"11_FLOW\",0),nvl(B.\"12_FLOW\",0),nvl(B.\"13_FLOW\",0),nvl(B.\"14_FLOW\",0),nvl(B.\"15_FLOW\",0),nvl(B.\"16_FLOW\",0)," +
                    "  nvl(B.\"21_FLOW\",0),nvl(B.\"22_FLOW\",0),nvl(B.\"23_FLOW\",0),nvl(B.\"24_FLOW\",0),nvl(B.\"25_FLOW\",0),nvl(B.\"26_FLOW\",0)," +
                    "  nvl(B.\"1_FEE\",0),nvl(B.\"2_FEE\",0),nvl(B.\"3_FEE\",0),nvl(B.\"4_FEE\",0)," +
                    "  nvl(B.\"11_FEE\",0),nvl(B.\"12_FEE\",0),nvl(B.\"13_FEE\",0),nvl(B.\"14_FEE\",0),nvl(B.\"15_FEE\",0),nvl(B.\"16_FEE\",0)," +
                    "  nvl(B.\"21_FEE\",0),nvl(B.\"22_FEE\",0),nvl(B.\"23_FEE\",0),nvl(B.\"24_FEE\",0),nvl(B.\"25_FEE\",0),nvl(B.\"26_FEE\",0)) ";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架统计分析]合并更新出口门架车流量和通行费(条):" + count);

            //最后清除临时表数据
            sqlPro = "delete from TB_SYNC_GBUPLOAD_ETCTU_TMP";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]清除临时表ETCTU(条):"+count);
            sqlPro = "delete from TB_STATIS_GRANTRY_BATCH_TMP";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]清除临时表BATCH(条):"+count);
            sqlPro = "delete from TB_SYNC_GBUPLOAD_ETCSU_TMP1";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]清除临时表ETCSU1(条):"+count);
            sqlPro = "delete from TB_SYNC_GBUPLOAD_ETCSU_TMP2";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架完整性]清除临时表ETCSU2(条):"+count);
            sqlPro = "delete from TB_STATIS_GRANTRY_CARFLOW_TMP";
            count = DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[门架统计分析]清除临时表COUNTFLOW(条):"+count);

            connPre.commit();

            sqlPro = "truncate table TB_SYNC_GBUPLOAD_ETCTU_TMP";
            DBUtils.execute(connPre, sqlPro);
            sqlPro = "truncate table TB_STATIS_GRANTRY_BATCH_TMP";
            DBUtils.execute(connPre, sqlPro);
            sqlPro = "truncate table TB_SYNC_GBUPLOAD_ETCSU_TMP1";
            DBUtils.execute(connPre, sqlPro);
            sqlPro = "truncate table TB_SYNC_GBUPLOAD_ETCSU_TMP2";
            DBUtils.execute(connPre, sqlPro);
            sqlPro = "truncate table TB_STATIS_GRANTRY_CARFLOW_TMP";
            DBUtils.execute(connPre, sqlPro);
            XxlJobLogger.log("[普通]重置临时表成功");

            connPre.commit();

            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
}
